from datetime import datetime

from flask import blueprints, render_template, request, jsonify, send_file
from flask_login import login_required, current_user
import pandas as pd
from io import BytesIO

from exts import db
from models import TopicModel

bp = blueprints.Blueprint('queMan', __name__, url_prefix='/queMan')


@bp.route('/getTopicsById/', methods=['GET'])
@login_required
def getTopicsById():
    user_info = request.args.get('q', default='', type=str)
    user = current_user
    print(user_info)
    # 对所有题目、内容、类型、难度、时间进行模糊查询
    topics = TopicModel.query.filter(
        (TopicModel.title.like('%' + user_info + '%') |
         TopicModel.content.like('%' + user_info + '%') |
         TopicModel.type.like('%' + user_info + '%') |
         TopicModel.difficulty.like('%' + user_info + '%') |
         TopicModel.create_time.like('%' + user_info + '%')) &
        (TopicModel.user_id == user.id)
    ).all()

    # print(topics)
    print(user.id)
    return render_template('index.html', TopicModels=topics, user_name=current_user.username, email=current_user.email)


@bp.route('/deletTopic', methods=['POST'])
@login_required
def deletTopic():
    data = request.get_json()
    user_id = data.get('id')
    topic = TopicModel.query.filter(
        (TopicModel.id == user_id) &
        (TopicModel.user_id == current_user.id)
    ).first()

    if topic:
        db.session.delete(topic)
        db.session.commit()
        return jsonify({'code': 200, 'message': "success"})
    return jsonify({'code': 404, 'message': "not found topic " + str(user_id)})


@bp.route('/upLoadTopic', methods=['GET', 'POST'])
@login_required
def updateTopic():
    if request.method == 'POST':
        data = request.get_json()  # 获取表单数据
        question_id = data.get('id')  # 假设你在前端传入了题目 ID
        print(data)

        if question_id:  # 如果题目 ID 存在，则更新
            question = TopicModel.query.filter(
                TopicModel.id == question_id |
                TopicModel.user_id == current_user.id
            ).first()
            if question:
                question.title = data['title']
                question.type = data['type']
                question.options = data['options']  # 获取选项列表
                question.content = data['content']
                question.answer = data['answer']
                question.difficulty = data['difficulty']
                question.explanation = data['explanation']
                db.session.commit()
                return jsonify({'message': '题目更新成功'}), 200
            return jsonify({'message': '题目未找到'}), 404
        else:  # 否则为新增题目
            new_question = TopicModel(
                title=data['title'],
                type=data['type'],
                options=data['options'],  # 获取选项列表
                content=data['content'],
                answer=data['answer'],
                difficulty=data['difficulty'],
                explanation=data['explanation'],
                user_id=current_user.id
            )
            db.session.add(new_question)
            db.session.commit()
            return jsonify({'message': '题目提交成功'}), 200

    return render_template('uploadTopic.html', isEdit=0, user_name=current_user.username, email=current_user.email)


@bp.route('/editTopic/<int:id>', methods=['GET'])
@login_required
def editTopic(id):
    question = TopicModel.query.get(id)
    if question:
        return render_template('uploadTopic.html', question=question, isEdit=1, user_name=current_user.username,
                               email=current_user.email)
    return jsonify({'message': '题目未找到'}), 404


# 导出题目到Excel文件
@bp.route('/exportTopic', methods=['GET'])
@login_required
def exportTopic():
    # 查询数据库中的所有题目
    topics = TopicModel.query.all()

    # 将查询结果转换为字典列表
    data = [
        {
            '账户': current_user.username,
            '标题': topic.title,
            '类型': topic.type,
            '选项': topic.options,
            '内容': topic.content,
            '难度': topic.difficulty,
            '正确答案': topic.answer,
            '解析': topic.explanation,
            '创建时间': topic.create_time
        }
        for topic in topics
    ]

    # 使用pandas创建DataFrame
    df = pd.DataFrame(data)

    # 将DataFrame写入Excel文件
    output = BytesIO()
    with pd.ExcelWriter(output, engine='openpyxl') as writer:
        df.to_excel(writer, index=False, sheet_name='Topics')

    # 将BytesIO对象重置到开始位置
    output.seek(0)

    # 设置文件名和内容类型
    filename = str(datetime.now().date()) + "_" + current_user.username + '.xlsx'
    content_type = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'

    # 返回文件
    return send_file(output, as_attachment=True, download_name=filename, mimetype=content_type)